# coding=utf-8 ################################################################
import sqlite3
import os
import struct
import utils

def get():
    newdb = False
    if not os.access("database", os.R_OK):
        newdb = True

    db = sqlite3.connect("database")
    db.isolation_level = None
    if newdb:
        create_database(db)
    return db

def create_database(db):
    db.execute("CREATE TABLE entries ("+
               "id INTEGER PRIMARY KEY, "+
               "data BLOB NOT NULL, "+
               "hash BLOB NOT NULL UNIQUE, "+
               "first_seen INTEGER NOT NULL)")

    db.execute("CREATE TABLE peers ("+
               "id INTEGER PRIMARY KEY, "+
               "ip BLOB NOT NULL, "+
               "port INTEGER NOT NULL, "+
               "last_seen INTEGER NOT NULL, "
               "UNIQUE (ip, port))")

    db.execute("CREATE TABLE votes ("+
               "id INTEGER PRIMARY KEY, "+
               "peer INTEGER NOT NULL, "+
               "entry INTEGER NOT NULL, "+
               "value REAL NOT NULL, "+
               "time INTEGER NOT NULL, "+
               "UNIQUE(peer, entry))")

    db.execute("CREATE TABLE my_votes ("+
               "id INTEGER PRIMARY KEY, "+
               "entry INTEGER NOT NULL UNIQUE, "+
               "value INTEGER NOT NULL, "+
               "time INTEGER NOT NULL)")

    db.execute("CREATE TABLE misc ("+
               "id INTEGER PRIMARY KEY, "+
               "name TEXT NOT NULL, "+
               "value TEXT NOT NULL)")
    
    # These tables are only for searching
    for cat in utils.categories:
        db.execute("CREATE VIRTUAL TABLE entries_fts_%s "%(cat,)+
                   "USING fts4 (title)")
    for main_cat in utils.main_categories:
        db.execute("CREATE VIRTUAL TABLE entries_fts_%s_all "%(main_cat,)+
                   "USING fts4 (title)")
    db.execute("CREATE VIRTUAL TABLE entries_fts_all "+
               "USING fts4 (title)")
    db.execute("CREATE TABLE entries_info ("+
               "id INTEGER PRIMARY KEY, "+
               "entry INTEGER UNIQUE, "+
               "magnet TEXT, "+
               "title TEXT, "+
               "creator TEXT, "+
               "text TEXT, "+
               "category INTEGER, "+
               "size INTEGER, "+
               "num_files INTEGER, "+
               "created INTEGER, "+
               "author BLOB, "+
               "author_rating REAL, "+
               "rating REAL)")
    db.execute("CREATE TABLE authors ("+
               "id INTEGER PRIMARY KEY, "+
               "externalid TEXT UNIQUE, "
               "rating REAL)")

    db.executemany("INSERT INTO misc (name, value) VALUES (?, ?)", 
                   [("port", "9999"), ("password", "explorer")])
